package cn.butcher21.dao.DaoWorkImpl;

import cn.butcher21.dao.DaoWork;
import cn.butcher21.pojo.*;
import cn.butcher21.utils.DButil;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * butcher create in 2021/04/10/17:08
 */
public class DaoWorkImpl implements DaoWork {
    @Override
    public List<Work> getMsg() throws SQLException {
        DButil dButil = new DButil();
        String sql = "SELECT\n" +
                "id,\n" +
                "Wname,\n" +
                "describe_w,\n" +
                "responsible_w,\n" +
                "checkMan,\n" +
                "START_time,\n" +
                "END_time,\n" +
                "progress \n" +
                "FROM\n" +
                "tasks\n" +
                "WHERE\n" +
                "STATUS = '0' \n";

        List<Work> list = new ArrayList<>();
        try {
            PreparedStatement ps = dButil.getPs(sql);
            ResultSet rs = dButil.getRs(ps.executeQuery());
            while (rs.next()) {
                Work work = new Work();
                work.setId(rs.getString("id"));
                work.setName(rs.getString("Wname"));
                work.setDescribe(rs.getString("describe_w"));
                work.setResponsible(rs.getString("responsible_w"));
                work.setCheckMan(rs.getString("checkMan"));
                work.setStart(rs.getString("START_time"));
                work.setEnd(rs.getString("END_time"));
                work.setProgress(rs.getInt("progress"));
                list.add(work);
            }
        } finally {
            dButil.closeAll();
        }
        return list;
    }

    @Override
    public void complete(String id) throws SQLException {
        DButil dButil = new DButil();
        String sql = "update tasks set status='1' where id=?";
        try {
            PreparedStatement ps = dButil.getPs(sql);
            ps.setString(1, id);
            ps.executeUpdate();
        } finally {
            dButil.closeAll();
        }
    }

    @Override
    public void click(String id, Click click) throws SQLException {
        DButil dButil = new DButil();
        String sql = "insert into day_progress(day,message,id_w) VALUES(?,?,?)";

        try {
            PreparedStatement ps = dButil.getPs(sql);
            ps.setString(1, click.getDay());
            ps.setString(2, click.getMessage());
            ps.setString(3, id);
            ps.execute();

            sql = "update tasks set progress=? where id=?";

            ps = dButil.getPs(sql);
            ps.setInt(1, Integer.parseInt(click.getProgress()));
            ps.setString(2, id);
            ps.execute();
        } finally {
            dButil.closeAll();
        }
    }

    @Override
    public List<Click> lookClick(String id) throws SQLException {
        DButil dButil = new DButil();
        String sql = "select day,message,evaluate from day_progress where id_w=?";
        List<Click> list = new ArrayList<>();
        try {
            PreparedStatement ps = dButil.getPs(sql);
            ps.setString(1, id);
            ResultSet rs = dButil.getRs(ps.executeQuery());

            while (rs.next()) {
                Click click = new Click();
                click.setDay(rs.getString("day"));
                click.setMessage(rs.getString("message"));
                click.setEvaluateMsg(rs.getString("evaluate"));
                list.add(click);
            }
        } finally {
            dButil.closeAll();
        }
        return list;
    }

    @Override
    public void add(Work work) throws SQLException {
        DButil dButil = new DButil();
        String sql = "INSERT INTO tasks ( Wname, describe_w, responsible_w, checkMan, START_time, END_time ,p_id ) VALUES ( ?, ?, ?, ?, ?, ? ,?)";
        try {
            PreparedStatement ps = dButil.getPs(sql);
            ps.setString(1, work.getName());
            ps.setString(2, work.getDescribe());
            ps.setString(3, work.getResponsible());
            ps.setString(4, work.getCheckMan());
            ps.setString(5, work.getStart());
            ps.setString(6, work.getEnd());
            ps.setString(7, work.getPid());
            ps.execute();
        } finally {
            dButil.closeAll();
        }
    }

    @Override
    public List<Work> getMsg(String num, String gid) throws SQLException {
        DButil dButil = new DButil();

        if (num == null) {
            String sql = "select max(p_id) from projects GROUP BY gid HAVING gid = ?";
            PreparedStatement ps = dButil.getPs(sql);
            ps.setString(1, gid);
            ResultSet rs = dButil.getRs(ps.executeQuery());
            if (rs.next()) {
                num = rs.getString(1);
            }
        }

        String sql = "SELECT\n" +
                " id,\n" +
                " Wname,\n" +
                " describe_w,\n" +
                " responsible_w,\n" +
                " checkMan,\n" +
                " START_time,\n" +
                " END_time,\n" +
                " progress \n" +
                "FROM\n" +
                " tasks w\n" +
                " JOIN projects p ON w.p_id = p.p_id \n" +
                "WHERE\n" +
                " STATUS = '0' \n" +
                " AND w.p_id = ? \n" +
                " AND p.p_status = '0'" +
                "AND p.gid=?";

        List<Work> list = new ArrayList<>();
        try {

            PreparedStatement ps = dButil.getPs(sql);
            ps.setString(1, num);
            ps.setInt(2, Integer.parseInt(gid));
            ResultSet rs = dButil.getRs(ps.executeQuery());
            while (rs.next()) {
                Work work = new Work();
                work.setId(rs.getString("id"));
                work.setName(rs.getString("Wname"));
                work.setDescribe(rs.getString("describe_w"));
                work.setResponsible(rs.getString("responsible_w"));
                work.setCheckMan(rs.getString("checkMan"));
                work.setStart(rs.getString("START_time"));
                work.setEnd(rs.getString("END_time"));
                work.setProgress(rs.getInt("progress"));
                list.add(work);
            }
        } finally {
            dButil.closeAll();
        }
        return list;
    }

    @Override
    public void add(Project project, String gid) throws SQLException {
        DButil dButil = new DButil();
        String sql = "insert into projects (p_name,p_manager,gid) VALUES (?,?,?)";
        PreparedStatement ps = dButil.getPs(sql);
        try {
            ps.setString(1, project.getName());
            ps.setString(2, project.getManager());
            ps.setString(3, gid);
            ps.executeUpdate();
        } finally {
            dButil.closeAll();
        }
    }

    public static void main(String[] args) throws SQLException {
        List<GroupTask> tasks = new DaoWorkImpl().getGroupTasks("2021-06-24");
        System.out.println(tasks);
    }

    /**
     * 通过传入的日期，获取每个小组每天的打卡
     *
     * @param date 日期
     * @return 信息
     * @throws SQLException 异常
     */
    @Override
    public List<GroupTask> getGroupTasks(String date) throws SQLException {
        DButil dButil = new DButil();
        List<GroupTask> groupTasks = new ArrayList<>();

        String sql = "select g.gid,g.gname,p.p_name,p.p_id FROM groups g left JOIN projects p ON g.gid = p.gid where p.p_id in (select max(p_id) from projects GROUP BY gid)  order by g.gid";
        PreparedStatement ps = dButil.getPs(sql);
        ResultSet rs = dButil.getRs(ps.executeQuery());
        while (rs.next()) {
            GroupTask groupTask = new GroupTask();
            groupTask.setDate(date);
            groupTask.setId(rs.getString("g.gid"));
            groupTask.setName(rs.getString("g.gname"));
            groupTask.setProject(rs.getString("p.p_name"));
            groupTask.setPid(rs.getString("p.p_id"));
            groupTasks.add(groupTask);
        }


        for (GroupTask task : groupTasks) {
            List<GroupMemberTask> groupMemberTasks = new ArrayList<>();
            sql = "SELECT\n" +
                    "\tt.p_id,\n" +
                    "\tp.`day`,\n" +
                    "\tt.responsible_w,\n" +
                    "\tt.describe_w,\n" +
                    "\tp.message ,\n" +
                    "\tt.progress\n" +
                    "FROM\n" +
                    "\t`day_progress` p\n" +
                    "\tJOIN `tasks` t ON p.id_w = t.id \n" +
                    "WHERE\n" +
                    "\tp.`day` = ? \n" +
                    "\tAND t.p_id = ?";
            ps = dButil.getPs(sql);
            ps.setString(1, date);
            ps.setString(2, task.getPid());
            rs = dButil.getRs(ps.executeQuery());
            while (rs.next()) {
                GroupMemberTask groupMemberTask = new GroupMemberTask();
                groupMemberTask.setName(rs.getString("t.responsible_w"));
                groupMemberTask.setTask(rs.getString("t.describe_w"));
                groupMemberTask.setReport(rs.getString("p.message"));
                groupMemberTask.setProcess(rs.getInt("t.progress"));
                groupMemberTasks.add(groupMemberTask);
            }
            sql = "SELECT DISTINCT\n" +
                    "\tevaluate \n" +
                    "FROM\n" +
                    "\tday_progress \n" +
                    "WHERE\n" +
                    "\tid_w IN (\n" +
                    "\tSELECT\n" +
                    "\t\tid \n" +
                    "\tFROM\n" +
                    "\t\ttasks \n" +
                    "\tWHERE\n" +
                    "\tp_id IN ( SELECT MAX( p_id ) FROM projects GROUP BY gid HAVING gid = ? )) \n" +
                    "\tAND `day` = ?";
            ps = dButil.getPs(sql);
            ps.setInt(1, Integer.parseInt(task.getId()));
            ps.setString(2, task.getDate());
            rs = dButil.getRs(ps.executeQuery());
            if (rs.next()) {
                task.setEvaluateMsg(rs.getString(1));
            } else {
                task.setEvaluateMsg("");
            }
            task.setGroupMemberTask(groupMemberTasks);
        }
        dButil.closeAll();
        return groupTasks;
    }

    @Override
    public void update(String id, String date, String msg) throws SQLException {
        DButil dButil = new DButil();
        String sql = "UPDATE `day_progress` a, ( SELECT id  FROM `day_progress` WHERE id_w IN ( SELECT id FROM tasks WHERE p_id = ? ) AND `day` = ? ) b\n" +
                "SET evaluate = ? \n" +
                "WHERE\n" +
                "\ta.id = b.id ;";
        PreparedStatement ps = dButil.getPs(sql);
        ps.setInt(1, Integer.parseInt(id));
        ps.setString(2, date);
        ps.setString(3, msg);
        ps.executeUpdate();
        dButil.closeAll();
    }

    /**
     * 获取老师的评价
     *
     * @param gid  小组id
     * @param date 日期
     * @return 评价信息
     * @throws SQLException 异常
     */
    @Override
    public Map<String, String> getTeacherEvaluateMsg(String gid, String date) throws SQLException {
        Map<String, String> map = new HashMap<>();
        DButil dButil = new DButil();
        String sql = "SELECT DISTINCT\n" +
                "\t`day`,\n" +
                "\tevaluate \n" +
                "FROM\n" +
                "\tday_progress \n" +
                "WHERE\n" +
                "\tid_w IN (\n" +
                "\tSELECT\n" +
                "\t\tid \n" +
                "\tFROM\n" +
                "\t\ttasks \n" +
                "\tWHERE\n" +
                "\tp_id IN ( SELECT MAX( p_id ) FROM projects GROUP BY gid HAVING gid = ? )) \n" +
                "\tAND `day` = ?";
        PreparedStatement ps = dButil.getPs(sql);
        ps.setInt(1, Integer.parseInt(gid));
        ps.setString(2, date);
        ResultSet rs = dButil.getRs(ps.executeQuery());
        if (rs.next() && rs.getString(2) != null) {
            map.put("title", rs.getString(1));
            map.put("content", rs.getString(2));
        } else {
            map.put("title", "");
            map.put("content", "");
        }
        return map;
    }
}
